﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SqlClient;

using SIPO.Library;
using SIPO.Library.DomainObject.Administration.Catalog;
using SIPO.Library.DomainObject.Administration.UserManagement;
using SIPO.Library.DomainObject.Transaction;
using SIPO.Library.Enum;
using SIPO.Library.Interfaces;

namespace SIPO.Library.DataAccess.Transaction
{
    public partial class recJurnalRepo : ISearchable<recJurnal>
    {
        public object SelectScalar(SQL.Function.Aggregate function, string column, string parameters)
        {
            object _result = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder();
                    switch (function)
                    {
                        case SQL.Function.Aggregate.Max:
                            sbQuery.AppendFormat("SELECT MAX({0}) from transactions.recJurnal {1}", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Min:
                            sbQuery.AppendFormat("SELECT MIN({0}) from transactions.recJurnal {1}", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Distinct:
                            sbQuery.AppendFormat("SELECT DISTINCT({0}) from transactions.recJurnal {1}", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Count:
                            sbQuery.AppendFormat("SELECT COUNT({0}) from transactions.recJurnal {1}", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Sum:
                            sbQuery.AppendFormat("SELECT SUM({0}) from transactions.recJurnal {1}", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Avg:
                            sbQuery.AppendFormat("SELECT AVG({0}) from transactions.recJurnal {1}", column, parameters);
                            break;
                        default:
                            // do nothing 
                            break;
                    }

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        _result = cmd.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return _result;
        }
        public object SelectScalarDetail(SQL.Function.Aggregate function, string column, string parameters)
        {
            object _result = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder();
                    switch (function)
                    {
                        case SQL.Function.Aggregate.Max:
                            sbQuery.AppendFormat("SELECT MAX({0}) from transactions.recJurnalDetail {1}", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Min:
                            sbQuery.AppendFormat("SELECT MIN({0}) from transactions.recJurnalDetail {1}", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Distinct:
                            sbQuery.AppendFormat("SELECT DISTINCT({0}) from transactions.recJurnalDetail {1}", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Count:
                            sbQuery.AppendFormat("SELECT COUNT({0}) from transactions.recJurnalDetail {1}", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Sum:
                            sbQuery.AppendFormat("SELECT SUM({0}) from transactions.recJurnalDetail {1}", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Avg:
                            sbQuery.AppendFormat("SELECT AVG({0}) from transactions.recJurnalDetail {1}", column, parameters);
                            break;
                        default:
                            // do nothing 
                            break;
                    }

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        _result = cmd.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return _result;
        }

        public List<recJurnal> GetList(int pageSize, int currentPage, string sortName, string sortOrder, string parameters)
        {
            List<recJurnal> tt = new List<recJurnal>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    int startRow = ((currentPage - 1) * pageSize) + 1;
                    int endRow = (startRow + pageSize);

                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat(" WITH result_set AS ");
                    sbQuery.AppendFormat(" ( ");
                    sbQuery.AppendFormat("    SELECT ");
                    sbQuery.AppendFormat("      ROW_NUMBER() OVER (ORDER BY {0} {1}) AS [row_number], ", sortName, sortOrder);
                    sbQuery.AppendFormat("      JurnalID, tipe_trans, CoaType, tgl_trans, no_ref1, no_ref2, no_ref3, thnbln, kd_kartu, nama, alamat, kd_valuta, kurs_valuta, jml_val_trans, jml_rp_trans, no_fakt_pajak, tgl_posting, TransName, no_posting, keterangan, rek_attribute1, rek_attribute2, cek_post, verifikasi, Last_create_date, Last_created_by, Last_update_date, Last_updated_by, Program_name, jml_cetak, tgl_cetak, JurnalCode ");
                    sbQuery.AppendFormat("    FROM ");
                    sbQuery.AppendFormat("      transactions.recJurnal ");
                    sbQuery.AppendFormat("    {0}", parameters);
                    sbQuery.AppendFormat(" ) ");
                    sbQuery.AppendFormat(" SELECT * FROM result_set WHERE [row_number] BETWEEN {0} AND {1} ", startRow, endRow);

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recJurnal t = new recJurnal();
                                t.JurnalID = dr[recJurnal.Fields.JurnalID] == DBNull.Value ? 0 : int.Parse(dr[recJurnal.Fields.JurnalID].ToString());
                                t.tipetrans = dr[recJurnal.Fields.tipetrans] == DBNull.Value ? string.Empty : dr[recJurnal.Fields.tipetrans].ToString();
                                t.CoaType = dr[recJurnal.Fields.CoaType] == DBNull.Value ? string.Empty : dr[recJurnal.Fields.CoaType].ToString();
                                t.tgltrans = dr[recJurnal.Fields.tgltrans] == DBNull.Value ? DateTime.Now : DateTime.Parse(dr[recJurnal.Fields.tgltrans].ToString());
                                t.noref1 = dr[recJurnal.Fields.noref1] == DBNull.Value ? string.Empty : dr[recJurnal.Fields.noref1].ToString();
                                t.noref2 = dr[recJurnal.Fields.noref2] == DBNull.Value ? string.Empty : dr[recJurnal.Fields.noref2].ToString();
                                t.noref3 = dr[recJurnal.Fields.noref3] == DBNull.Value ? string.Empty : dr[recJurnal.Fields.noref3].ToString();
                                t.thnbln = dr[recJurnal.Fields.thnbln] == DBNull.Value ? string.Empty : dr[recJurnal.Fields.thnbln].ToString();
                                t.kdkartu = dr[recJurnal.Fields.kdkartu] == DBNull.Value ? string.Empty : dr[recJurnal.Fields.kdkartu].ToString();
                                t.nama = dr[recJurnal.Fields.nama] == DBNull.Value ? string.Empty : dr[recJurnal.Fields.nama].ToString();
                                t.alamat = dr[recJurnal.Fields.alamat] == DBNull.Value ? string.Empty : dr[recJurnal.Fields.alamat].ToString();
                                t.kdvaluta = dr[recJurnal.Fields.kdvaluta] == DBNull.Value ? string.Empty : dr[recJurnal.Fields.kdvaluta].ToString();
                                t.kursvaluta = dr[recJurnal.Fields.kursvaluta] == DBNull.Value ? 0 : decimal.Parse(dr[recJurnal.Fields.kursvaluta].ToString());
                                t.jmlvaltrans = dr[recJurnal.Fields.jmlvaltrans] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recJurnal.Fields.jmlvaltrans].ToString());
                                t.jmlrptrans = dr[recJurnal.Fields.jmlrptrans] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recJurnal.Fields.jmlrptrans].ToString());
                                t.nofaktpajak = dr[recJurnal.Fields.nofaktpajak] == DBNull.Value ? string.Empty : dr[recJurnal.Fields.nofaktpajak].ToString();
                                t.tglposting = dr[recJurnal.Fields.tglposting] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recJurnal.Fields.tglposting].ToString());
                                t.TransName = dr[recJurnal.Fields.TransName] == DBNull.Value ? string.Empty : dr[recJurnal.Fields.TransName].ToString();
                                t.noposting = dr[recJurnal.Fields.noposting] == DBNull.Value ? string.Empty : dr[recJurnal.Fields.noposting].ToString();
                                t.keterangan = dr[recJurnal.Fields.keterangan] == DBNull.Value ? string.Empty : dr[recJurnal.Fields.keterangan].ToString();
                                t.rekattribute1 = dr[recJurnal.Fields.rekattribute1] == DBNull.Value ? string.Empty : dr[recJurnal.Fields.rekattribute1].ToString();
                                t.rekattribute2 = dr[recJurnal.Fields.rekattribute2] == DBNull.Value ? string.Empty : dr[recJurnal.Fields.rekattribute2].ToString();
                                t.cekpost = dr[recJurnal.Fields.cekpost] == DBNull.Value ? string.Empty : dr[recJurnal.Fields.cekpost].ToString();
                                t.verifikasi = dr[recJurnal.Fields.verifikasi] == DBNull.Value ? string.Empty : dr[recJurnal.Fields.verifikasi].ToString();
                                t.Lastcreatedate = dr[recJurnal.Fields.Lastcreatedate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recJurnal.Fields.Lastcreatedate].ToString());
                                t.Lastcreatedby = dr[recJurnal.Fields.Lastcreatedby] == DBNull.Value ? string.Empty : dr[recJurnal.Fields.Lastcreatedby].ToString();
                                t.Lastupdatedate = dr[recJurnal.Fields.Lastupdatedate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recJurnal.Fields.Lastupdatedate].ToString());
                                t.Lastupdatedby = dr[recJurnal.Fields.Lastupdatedby] == DBNull.Value ? string.Empty : dr[recJurnal.Fields.Lastupdatedby].ToString();
                                t.Programname = dr[recJurnal.Fields.Programname] == DBNull.Value ? string.Empty : dr[recJurnal.Fields.Programname].ToString();
                                t.jmlcetak = dr[recJurnal.Fields.jmlcetak] == DBNull.Value ? (int?)null : int.Parse(dr[recJurnal.Fields.jmlcetak].ToString());
                                t.tglcetak = dr[recJurnal.Fields.tglcetak] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recJurnal.Fields.tglcetak].ToString());
                                t.JurnalCode = dr[recJurnal.Fields.JurnalCode] == DBNull.Value ? string.Empty : dr[recJurnal.Fields.JurnalCode].ToString();
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }
        public List<recJurnalDetail> GetListDetail(int pageSize, int currentPage, string sortName, string sortOrder, string parameters)
        {
            List<recJurnalDetail> tt = new List<recJurnalDetail>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    int startRow = ((currentPage - 1) * pageSize) + 1;
                    int endRow = (startRow + pageSize);

                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat(" WITH result_set AS ");
                    sbQuery.AppendFormat(" ( ");
                    sbQuery.AppendFormat("    SELECT ");
                    sbQuery.AppendFormat("      ROW_NUMBER() OVER (ORDER BY {0} {1}) AS [row_number], ", sortName, sortOrder);
                    sbQuery.AppendFormat("      JurnalDetailID, JurnalCode, tipe_trans, CoaType, no_seq, kd_buku_besar,buku_besar_name, kartu, kd_buku_pusat, tgl_valuta, kd_valuta, kurs_valuta, saldo_val_debet, saldo_val_kredit, saldo_rp_debet, saldo_rp_kredit, keterangan, status, prev_no_inv, kd_obyek, no_ref1, no_ref2, no_ref3, val_ref1, harga, tinggi, lebar, panjang, Last_create_date, Last_created_by, Last_update_date, Last_updated_by, Programe_name ");
                    sbQuery.AppendFormat("    FROM ");
                    sbQuery.AppendFormat("      transactions.recJurnalDetail ");
                    sbQuery.AppendFormat("    {0}", parameters);
                    sbQuery.AppendFormat(" ) ");
                    sbQuery.AppendFormat(" SELECT * FROM result_set WHERE [row_number] BETWEEN {0} AND {1} ", startRow, endRow);

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recJurnalDetail t = new recJurnalDetail();
                                t.JurnalDetailID = dr[recJurnalDetail.Fields.JurnalDetailID] == DBNull.Value ? 0 : int.Parse(dr[recJurnalDetail.Fields.JurnalDetailID].ToString());
                                t.JurnalCode = dr[recJurnalDetail.Fields.JurnalCode] == DBNull.Value ? string.Empty : dr[recJurnalDetail.Fields.JurnalCode].ToString();
                                t.tipetrans = dr[recJurnalDetail.Fields.tipetrans] == DBNull.Value ? string.Empty : dr[recJurnalDetail.Fields.tipetrans].ToString();
                                t.CoaType = dr[recJurnalDetail.Fields.CoaType] == DBNull.Value ? string.Empty : dr[recJurnalDetail.Fields.CoaType].ToString();
                                t.noseq = dr[recJurnalDetail.Fields.noseq] == DBNull.Value ? 0 : int.Parse(dr[recJurnalDetail.Fields.noseq].ToString());
                                t.kdbukubesar = dr[recJurnalDetail.Fields.kdbukubesar] == DBNull.Value ? string.Empty : dr[recJurnalDetail.Fields.kdbukubesar].ToString();
                                t.kartu = dr[recJurnalDetail.Fields.kartu] == DBNull.Value ? string.Empty : dr[recJurnalDetail.Fields.kartu].ToString();
                                t.kdbukupusat = dr[recJurnalDetail.Fields.kdbukupusat] == DBNull.Value ? string.Empty : dr[recJurnalDetail.Fields.kdbukupusat].ToString();
                                t.tglvaluta = dr[recJurnalDetail.Fields.tglvaluta] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recJurnalDetail.Fields.tglvaluta].ToString());
                                t.kdvaluta = dr[recJurnalDetail.Fields.kdvaluta] == DBNull.Value ? string.Empty : dr[recJurnalDetail.Fields.kdvaluta].ToString();
                                t.kursvaluta = dr[recJurnalDetail.Fields.kursvaluta] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recJurnalDetail.Fields.kursvaluta].ToString());
                                t.saldovaldebet = dr[recJurnalDetail.Fields.saldovaldebet] == DBNull.Value ? 0 : decimal.Parse(dr[recJurnalDetail.Fields.saldovaldebet].ToString());
                                t.saldovalkredit = dr[recJurnalDetail.Fields.saldovalkredit] == DBNull.Value ? 0 : decimal.Parse(dr[recJurnalDetail.Fields.saldovalkredit].ToString());
                                t.saldorpdebet = dr[recJurnalDetail.Fields.saldorpdebet] == DBNull.Value ? 0 : decimal.Parse(dr[recJurnalDetail.Fields.saldorpdebet].ToString());
                                t.saldorpkredit = dr[recJurnalDetail.Fields.saldorpkredit] == DBNull.Value ? 0 : decimal.Parse(dr[recJurnalDetail.Fields.saldorpkredit].ToString());
                                t.keterangan = dr[recJurnalDetail.Fields.keterangan] == DBNull.Value ? string.Empty : dr[recJurnalDetail.Fields.keterangan].ToString();
                                t.status = dr[recJurnalDetail.Fields.status] == DBNull.Value ? string.Empty : dr[recJurnalDetail.Fields.status].ToString();
                                t.prevnoinv = dr[recJurnalDetail.Fields.prevnoinv] == DBNull.Value ? string.Empty : dr[recJurnalDetail.Fields.prevnoinv].ToString();
                                t.kdobyek = dr[recJurnalDetail.Fields.kdobyek] == DBNull.Value ? string.Empty : dr[recJurnalDetail.Fields.kdobyek].ToString();
                                t.noref1 = dr[recJurnalDetail.Fields.noref1] == DBNull.Value ? string.Empty : dr[recJurnalDetail.Fields.noref1].ToString();
                                t.noref2 = dr[recJurnalDetail.Fields.noref2] == DBNull.Value ? string.Empty : dr[recJurnalDetail.Fields.noref2].ToString();
                                t.noref3 = dr[recJurnalDetail.Fields.noref3] == DBNull.Value ? string.Empty : dr[recJurnalDetail.Fields.noref3].ToString();
                                t.valref1 = dr[recJurnalDetail.Fields.valref1] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recJurnalDetail.Fields.valref1].ToString());
                                t.harga = dr[recJurnalDetail.Fields.harga] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recJurnalDetail.Fields.harga].ToString());
                                t.tinggi = dr[recJurnalDetail.Fields.tinggi] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recJurnalDetail.Fields.tinggi].ToString());
                                t.lebar = dr[recJurnalDetail.Fields.lebar] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recJurnalDetail.Fields.lebar].ToString());
                                t.panjang = dr[recJurnalDetail.Fields.panjang] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recJurnalDetail.Fields.panjang].ToString());
                                t.Lastcreatedate = dr[recJurnalDetail.Fields.Lastcreatedate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recJurnalDetail.Fields.Lastcreatedate].ToString());
                                t.Lastcreatedby = dr[recJurnalDetail.Fields.Lastcreatedby] == DBNull.Value ? string.Empty : dr[recJurnalDetail.Fields.Lastcreatedby].ToString();
                                t.Lastupdatedate = dr[recJurnalDetail.Fields.Lastupdatedate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recJurnalDetail.Fields.Lastupdatedate].ToString());
                                t.Lastupdatedby = dr[recJurnalDetail.Fields.Lastupdatedby] == DBNull.Value ? string.Empty : dr[recJurnalDetail.Fields.Lastupdatedby].ToString();
                                t.Programename = dr[recJurnalDetail.Fields.Programename] == DBNull.Value ? string.Empty : dr[recJurnalDetail.Fields.Programename].ToString();
                                t.bukubesarname = dr[recJurnalDetail.Fields.bukubesarname] == DBNull.Value ? string.Empty : dr[recJurnalDetail.Fields.bukubesarname].ToString();
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }
        public List<recJurnal> GetListTanggal()
        {
            List<recJurnal> tt = new List<recJurnal>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                   
                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat(" WITH result_set AS ");
                    sbQuery.AppendFormat(" ( ");
                    sbQuery.AppendFormat("    SELECT ");
                    sbQuery.AppendFormat("     distinct(thnbln)");
                    sbQuery.AppendFormat("    FROM ");
                    sbQuery.AppendFormat("      transactions.recJurnal ");
                    sbQuery.AppendFormat(" ) ");
                    sbQuery.AppendFormat(" SELECT * FROM result_set ");

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recJurnal t = new recJurnal();
                                t.thnbln = dr[recJurnal.Fields.thnbln] == DBNull.Value ? string.Empty : dr[recJurnal.Fields.thnbln].ToString();
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }
        public object SelectScalarSummary(SQL.Function.Aggregate function, string column, string parameters)
        {
            object _result = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder();
                    switch (function)
                    {
                        case SQL.Function.Aggregate.Max:
                            sbQuery.AppendFormat("SELECT MAX(distinct {0}) FROM transactions.recJurnalSummary a  left join master.recTransactionType b on b.TransTypeCode=a.tipe_trans left join master.recMapCoa c on a.coatype=c.coatype {1}", column, parameters);                        
                            break;
                        case SQL.Function.Aggregate.Min:
                            sbQuery.AppendFormat("SELECT MIN(distinct {0}) FROM transactions.recJurnalSummary a  left join master.recTransactionType b on b.TransTypeCode=a.tipe_trans left join master.recMapCoa c on a.coatype=c.coatype {1}", column, parameters);                        
                            break;
                        case SQL.Function.Aggregate.Distinct:
                            sbQuery.AppendFormat("SELECT DISTINCT(distinct {0}) FROM transactions.recJurnalSummary a  left join master.recTransactionType b on b.TransTypeCode=a.tipe_trans left join master.recMapCoa c on a.coatype=c.coatype {1}", column, parameters);                        
                            break;
                        case SQL.Function.Aggregate.Count:
                            sbQuery.AppendFormat("SELECT COUNT(distinct {0}) FROM transactions.recJurnalSummary a  left join master.recTransactionType b on b.TransTypeCode=a.tipe_trans left join master.recMapCoa c on a.coatype=c.coatype {1}", column, parameters);                        
                            break;
                        case SQL.Function.Aggregate.Sum:
                            sbQuery.AppendFormat("SELECT SUM(distinct {0}) FROM transactions.recJurnalSummary a  left join master.recTransactionType b on b.TransTypeCode=a.tipe_trans left join master.recMapCoa c on a.coatype=c.coatype {1}", column, parameters);                        
                            break;
                        case SQL.Function.Aggregate.Avg:
                            sbQuery.AppendFormat("SELECT AVG(distinct {0}) FROM transactions.recJurnalSummary a  left join master.recTransactionType b on b.TransTypeCode=a.tipe_trans left join master.recMapCoa c on a.coatype=c.coatype {1}", column, parameters);                        
                            break;
                        default:
                            // do nothing 
                            break;
                    }

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        _result = cmd.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return _result;
        }
        public List<recJurnalSummary> GetListSummary(int pageSize, int currentPage, string sortName, string sortOrder, string parameters, int top)
        {
            List<recJurnalSummary> tt = new List<recJurnalSummary>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    int startRow = ((currentPage - 1) * pageSize) + 1;
                    int endRow = (startRow + pageSize);

                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat(" WITH result_set AS ");
                    sbQuery.AppendFormat(" ( ");
                    sbQuery.AppendFormat("    SELECT ");
                    sbQuery.AppendFormat("      ROW_NUMBER() OVER (ORDER BY {0} {1}) AS [row_number], ", sortName, sortOrder);
                    sbQuery.AppendFormat("    b.TransTypeName, a.PostingGroup, a.PostingDate, a.CreatedBy, c.coaTypeNmae");
                    sbQuery.AppendFormat("    FROM ");
                    sbQuery.AppendFormat(@"      transactions.recJurnalSummary a 
                                                 left join master.recTransactionType b on b.TransTypeCode=a.tipe_trans 
                                                 left join master.recMapCoa c on a.coatype=c.Mapcoaid");
                    sbQuery.AppendFormat("    {0}", parameters);
                    sbQuery.AppendFormat(" ) ");
                    sbQuery.AppendFormat(" SELECT top {0} * FROM result_set WHERE [row_number] BETWEEN {1} AND {2} ", top, startRow, endRow);

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recJurnalSummary t = new recJurnalSummary();
                                t.PostingGroup = dr[recJurnalSummary.Fields.PostingGroup] == DBNull.Value ? string.Empty : dr[recJurnalSummary.Fields.PostingGroup].ToString();
                                t.PostingDate = dr[recJurnalSummary.Fields.PostingDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recJurnalSummary.Fields.PostingDate].ToString());
                                t.coaTypeNmae = dr[recMapCoa.Fields.CoaTypeNmae] == DBNull.Value ? string.Empty : dr[recMapCoa.Fields.CoaTypeNmae].ToString();
                                t.CreatedBy = dr[recTransactionType.Fields.TransTypeName] == DBNull.Value ? string.Empty : dr[recTransactionType.Fields.TransTypeName].ToString();
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }
        public List<recJurnalSummary> GetListSummDetail(int pageSize, int currentPage, string sortName, string sortOrder, string parameters)
        {
            List<recJurnalSummary> tt = new List<recJurnalSummary>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    int startRow = ((currentPage - 1) * pageSize) + 1;
                    int endRow = (startRow + pageSize);

                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat(" WITH result_set AS ");
                    sbQuery.AppendFormat(" ( ");
                    sbQuery.AppendFormat("    SELECT ");
                    sbQuery.AppendFormat("      ROW_NUMBER() OVER (ORDER BY {0} {1}) AS [row_number], ", sortName, sortOrder);
                    sbQuery.AppendFormat("      SumID, PostingGroup, PostingDate, YearMonth, CodeAccount, tipe_trans, coaTypeNmae, coatype, SaikoNo, SaldoDebet, Saldokredit, IsToSaiko, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy ");
                    sbQuery.AppendFormat("    FROM ");
                    sbQuery.AppendFormat("      transactions.recJurnalSummary ");
                    sbQuery.AppendFormat("    {0}", parameters);
                    sbQuery.AppendFormat(" ) ");
                    sbQuery.AppendFormat(" SELECT * FROM result_set WHERE [row_number] BETWEEN {0} AND {1} ", startRow, endRow);

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recJurnalSummary t = new recJurnalSummary();
                                t.SumID = dr[recJurnalSummary.Fields.SumID] == DBNull.Value ? 0 : int.Parse(dr[recJurnalSummary.Fields.SumID].ToString());
                                t.PostingGroup = dr[recJurnalSummary.Fields.PostingGroup] == DBNull.Value ? string.Empty : dr[recJurnalSummary.Fields.PostingGroup].ToString();
                                t.PostingDate = dr[recJurnalSummary.Fields.PostingDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recJurnalSummary.Fields.PostingDate].ToString());
                                t.YearMonth = dr[recJurnalSummary.Fields.YearMonth] == DBNull.Value ? string.Empty : dr[recJurnalSummary.Fields.YearMonth].ToString();
                                t.CodeAccount = dr[recJurnalSummary.Fields.CodeAccount] == DBNull.Value ? string.Empty : dr[recJurnalSummary.Fields.CodeAccount].ToString();
                                t.tipetrans = dr[recJurnalSummary.Fields.tipetrans] == DBNull.Value ? 0 : int.Parse(dr[recJurnalSummary.Fields.tipetrans].ToString());
                                t.coaTypeNmae = dr[recJurnalSummary.Fields.coaTypeNmae] == DBNull.Value ? string.Empty : dr[recJurnalSummary.Fields.coaTypeNmae].ToString();
                                t.coatype = dr[recJurnalSummary.Fields.coatype] == DBNull.Value ? string.Empty : dr[recJurnalSummary.Fields.coatype].ToString();
                                t.SaikoNo = dr[recJurnalSummary.Fields.SaikoNo] == DBNull.Value ? string.Empty : dr[recJurnalSummary.Fields.SaikoNo].ToString();
                                t.SaldoDebet = dr[recJurnalSummary.Fields.SaldoDebet] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recJurnalSummary.Fields.SaldoDebet].ToString());
                                t.Saldokredit = dr[recJurnalSummary.Fields.Saldokredit] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recJurnalSummary.Fields.Saldokredit].ToString());
                                t.IsToSaiko = dr[recJurnalSummary.Fields.IsToSaiko] == DBNull.Value ? (bool?)null : bool.Parse(dr[recJurnalSummary.Fields.IsToSaiko].ToString());
                                t.CreatedDate = dr[recJurnalSummary.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recJurnalSummary.Fields.CreatedDate].ToString());
                                t.CreatedBy = dr[recJurnalSummary.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[recJurnalSummary.Fields.CreatedBy].ToString();
                                t.UpdatedDate = dr[recJurnalSummary.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recJurnalSummary.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr[recJurnalSummary.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[recJurnalSummary.Fields.UpdatedBy].ToString();
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }

        public int UpdateIsTosaiko(string ResID)
        {
            object result = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"update transactions.recJurnalSummary set istosaiko = 1 where postinggroup ='" + ResID + "'";
                    cmd.CommandType = CommandType.Text;

                    try
                    {
                        conn.Open();
                        cmd.ExecuteNonQuery();
                        result = 1;
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }

                }
            }

            return result == null ? -1 : int.Parse(result.ToString());
        }
        public List<recJurnalSummary> GetList(string parameters)
        {
            List<recJurnalSummary> tt = new List<recJurnalSummary>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT distinct PostingGroup FROM transactions.recJurnalSummary "+parameters;
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recJurnalSummary t = new recJurnalSummary();
                               t.PostingGroup = dr[recJurnalSummary.Fields.PostingGroup] == DBNull.Value ? string.Empty : dr[recJurnalSummary.Fields.PostingGroup].ToString();
                                 tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }		
    }
}
